using System.Collections.Generic;
using System.Linq;
using Dapper;
using LiteTools.service;

namespace LiteTools.domains.db
{
    public class TagInfoHelper
    {
        public class TagInfo
        {
            public string tag_id { set; get; }
            public string tag_family { set; get; }
            public string tag_value { set; get; }
            public string tag_desc { set; get; }
            public string tag_type { set; get; }
        }

        public static bool AddTagInfo(TagInfo tagInfo)
        {
            var connection = DbService.GetConnection();
            if (connection == null)
            {
                return false;
            }

            // 插入示例
            const string insertQuery =
                "INSERT INTO tag_info (tag_id, tag_family, tag_value, tag_desc, tag_type) VALUES (@ID, @FAMILY, @VALUE, @DESC, @TYPE)";
            var execute = connection.Execute(insertQuery,
                new
                {
                    ID = tagInfo.tag_id, FAMILY = tagInfo.tag_family, VALUE = tagInfo.tag_value,
                    DESC = tagInfo.tag_desc, TYPE = tagInfo.tag_type
                });
            var result = execute >= 1;
            connection.Close();
            return result;
        }

        public static bool RemoveTagInfo(string id)
        {
            var connection = DbService.GetConnection();
            if (connection == null)
            {
                return false;
            }

            // 删除示例
            const string deleteQuery = "DELETE FROM tag_info WHERE tag_id = @Id";
            var execute = connection.Execute(deleteQuery, new { Id = id });
            connection.Close();
            return execute == 1;
        }

        public static bool UpdateTagInfo(TagInfo tagInfo)
        {
            var connection = DbService.GetConnection();
            if (connection == null)
            {
                return false;
            }

            // 更新示例
            const string updateQuery =
                "UPDATE tag_info SET tag_id = @ID, tag_family = @FAMILY, tag_value = @VALUE,tag_desc = @DESC, tag_type = @TYPE WHERE tag_id = @ID";
            var execute = connection.Execute(updateQuery, new
            {
                ID = tagInfo.tag_id, FAMILY = tagInfo.tag_family, VALUE = tagInfo.tag_value,
                DESC = tagInfo.tag_desc, TYPE = tagInfo.tag_type
            });
            connection.Close();
            return execute == 1;
        }

        public static List<TagInfo> GetTagInfoByFamily(string family)
        {
            var connection = DbService.GetConnection();
            if (connection == null)
            {
                return null;
            }

            const string selectQuery = "SELECT * FROM tag_info WHERE tag_family = @FAMILY";
            var tagInfoList = connection.Query<TagInfo>(selectQuery, new { FAMILY = family }).ToList();
            connection.Close();
            return tagInfoList;
        }
        
        public static TagInfo GetTagInfoByFamilyAndValue(string family, string value)
        {
            var connection = DbService.GetConnection();
            if (connection == null)
            {
                return null;
            }

            const string selectQuery = "SELECT * FROM tag_info WHERE tag_family = @FAMILY and tag_value = @VALUE";
            var tagInfoList = connection.Query<TagInfo>(selectQuery, new { FAMILY = family, VALUE = value }).ToList();
            connection.Close();
            return tagInfoList.Count == 0 ? null : tagInfoList[0];
        }

        public static List<TagInfo> GetTagInfos()
        {
            var connection = DbService.GetConnection();
            const string selectQuery = "select tag_id, tag_family, tag_value, tag_desc, tag_type from tag_info";
            var tagInfoList = connection.Query<TagInfo>(selectQuery).ToList();
            connection.Close();
            return tagInfoList;
        }
    }
}